﻿using RndMaterialDisposalService.Models.RequestModel;
using RndMaterialDisposalService.Models;
using RndMaterialDisposalService.Services.Interface.SmallBatchTrial;
using RndMaterialDisposalService.Attrbutes;
using RndMaterialDisposalService.Repository;
using Microsoft.Data.SqlClient;
using System.Data;
using Masuit.Tools;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace RndMaterialDisposalService.Services.SmallBatchTrialService
{
    [NAutowired(Lifecycle.Scoped)]
    public class SmallBatchTrialService: ISmallBatchTrialService
    {
        private readonly AssetsManagementContext dbContext;
        private readonly UserBaseInforDataContext userBaseInforDataContext;
        private readonly WorkflowCommonDataContext workflowCommonDataContext;
        public SmallBatchTrialService(AssetsManagementContext _dbContext, UserBaseInforDataContext _userBaseInforDataContext, WorkflowCommonDataContext _workflowCommonDataContext)
        {
            dbContext = _dbContext;
            userBaseInforDataContext = _userBaseInforDataContext;
            workflowCommonDataContext = _workflowCommonDataContext;
        }

        /// <summary>
        /// 返回小批量试用跟踪电子流报表数据（带分页）
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public List<SmallBatchTrialReportFormModel> GetSmallBatchTrialReportFormPageList(SmallBatchTrialReportConditionModel Input) 
        {
            List<SmallBatchTrialReportFormModel> list = new List<SmallBatchTrialReportFormModel>();
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            string condition = "1=1 ";
            if (!string.IsNullOrWhiteSpace(Input.CurrentStatus)) 
            {
                condition += $"AND V.CurrentStatus = '{Input.CurrentStatus}' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.ApplicantEmployeeNumber)) 
            {
                condition += $"AND V.ApplicantEmployeeNumber = '{Input.ApplicantEmployeeNumber}' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.ProductLine))
            {
                condition += $"AND V.ProductLine = '{Input.ProductLine}' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.NewMaterialCode))
            {
                condition += $"AND V.NewMaterialCode LIKE '%{Input.NewMaterialCode}%' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.RawMaterialCode))
            {
                condition += $"AND V.RawMaterialCode LIKE '%{Input.RawMaterialCode}%' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.ApplicationNumber))
            {
                condition += $"AND V.ApplicationNumber LIKE '%{Input.ApplicationNumber}%' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.ApplicantTime))
            {
                string[] timeArr = Input.ApplicantTime.Split(',');
                if (timeArr.Length > 0)
                {
                    condition += $"AND V.ApplicantTime >= '{timeArr[0]} 00:00:00' AND V.ApplicantTime <= '{timeArr[1]} 23:59:59' ";
                }
            }
            if (!string.IsNullOrWhiteSpace(Input.TrialSource))
            {
                condition += $"AND V.TrialSource = '{Input.TrialSource}' ";
            }

            //if (!string.IsNullOrWhiteSpace(input.AssetNo))
            //{
            //    condition += $"AND V.AssetNo LIKE '%'+@AssetNo+'%' ";
            //    SqlParameter sqlPara = new SqlParameter("@AssetNo", SqlDbType.NVarChar);
            //    sqlPara.Value = input.AssetNo;
            //    sqlParameters.Add(sqlPara);
            //}

            string SqlInsertClip = string.Empty;

            List<WorkflowProcInstIdModel> ProcInstIdList = new List<WorkflowProcInstIdModel>();

            //插入当前用户查询业务
            if (!string.IsNullOrWhiteSpace(Input.CurrentUser))
            {
                //获取当前用户资产赔偿电子流审批流程记录,去重后联查得到ProcInstId
                string _sql = $"SELECT a.ProcInstId,c.ApplicationNumber,c.ApproveDate FROM AssetsManagement.dbo.SmallBatchTrialBusinessData a LEFT JOIN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = b.ApplicationNumber)  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData b WHERE WorkflowName = '小批量试用跟踪电子流' AND ApproverName = '{Input.CurrentUser}') c ON c.ApplicationNumber = a.ProcessFolio WHERE ProcInstId !='' AND ApproveDate IS NOT NULL";
                ProcInstIdList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());

                //审批历史ProcInstId
                var ApprovalHistoryProcInstIdList = ProcInstIdList.Select(a => a.ProcInstId).ToList();

                //当前用户工号
                string employeeNumber = Input.CurrentUser.Substring(0, Input.CurrentUser.IndexOf(" "));
                //利用UserID获取当前用户工号LaunchList
                //var EmployeeInfo = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(x => x.UserId == UserId);
                if (!string.IsNullOrWhiteSpace(employeeNumber))
                {
                    //获取发起流程实例
                    _sql = $"SELECT ProcInstId FROM VW_Common.dbo.WorkflowApplicationData WHERE ProcInstId !='' AND WorkflowName='小批量试用跟踪电子流' AND ApplicantEmployeeNumber = '{employeeNumber}'";
                    List<WorkflowProcInstIdModel> LaunchList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());
                    if (LaunchList.Any())
                    {
                        var LaunchProcInstIdList = LaunchList.Select(a => a.ProcInstId).ToList();
                        ApprovalHistoryProcInstIdList.AddRange(LaunchProcInstIdList);
                        ApprovalHistoryProcInstIdList = ApprovalHistoryProcInstIdList.Distinct().ToList();
                    }
                }

                string ProcInstId_STR = "''";
                if (ApprovalHistoryProcInstIdList.Any())
                {
                    List<string> _ProcInstIdList = ApprovalHistoryProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                    ProcInstId_STR = string.Join(',', _ProcInstIdList);
                }
                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.SmallBatchTrialBusinessSubtableData WHERE ProcInstId IN({ProcInstId_STR}))";
            }
            else
            {
                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.SmallBatchTrialBusinessSubtableData WHERE ProcInstId !='')";
            }

            int startIndex = ((Input.pageIndex - 1) * Input.pageSize) + 1;
            int endIndex = Input.pageSize * Input.pageIndex;

            string sql = @$"SELECT V.[Key],V.ApplicationNumber,V.ProcInstId,V.ApplicantEmployeeNumber,V.ApplicantName,V.CurrentStatus,V.ApplicantTime,V.SubmitBy, 
                                   V.NewMaterialCode,V.RawMaterialCode,V.TrialSource,V.SQEEngineer,
                                   V.ProductLine,V.TrialBoardCabinetCode,V.SingleBoardCabinetType,V.UsingSingleBoardCabinetCount
                                   
                            FROM (SELECT ROW_NUMBER() OVER(ORDER BY V.ApplicantTime DESC) AS RowID,V.* FROM (
                            SELECT subtable.SysId AS [Key],TRIM(workflow.ApplicationNumber)AS ApplicationNumber,workflow.ProcInstId,workflow.ApplicantEmployeeNumber,TRIM(workflow.CurrentHandler) AS ApplicantName,workflow.CurrentStatus,workflow.CreatedDate AS ApplicantTime,workflow.SubmitBy,
                                   host.NewMaterialCode,host.RawMaterialCode,host.TrialSource,host.SQEEngineer, 
                                   subtable.ProductLine,subtable.TrialBoardCabinetCode,subtable.SingleBoardCabinetType,subtable.UsingSingleBoardCabinetCount
                                  
                            FROM {SqlInsertClip} subtable 
                            LEFT JOIN AssetsManagement.dbo.SmallBatchTrialBusinessData host ON subtable.ProcInstId = host.ProcInstId  
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON subtable.ProcInstId = workflow.ProcInstId) V WHERE {condition}) V 
                            WHERE V.RowID BETWEEN {startIndex} AND {endIndex}";

            list = SqlQuery<SmallBatchTrialReportFormModel>(sql, sqlParameters);

            //赋值流程最早时间
            if (!string.IsNullOrWhiteSpace(Input.CurrentUser))
            {
                list.ForEach((x) => {
                    var item = ProcInstIdList.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                    if (item != null)
                    {
                        x.ApplicantTime = item.ApproveDate;
                    }
                });
            }

            ////去掉当前审批人前缀
            //list.ForEach((x) => {
            //    if (!string.IsNullOrWhiteSpace(x.ApplicantName))
            //    {
            //        x.ApplicantName = x.ApplicantName.Substring(x.ApplicantName.IndexOf(" ") + 1).Trim();
            //    }
            //});
            return list;
        }


        /// <summary>
        ///返回小批量试用跟踪电子流报表主数据
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public List<SmallBatchTrialReportFormModel> GetSmallBatchTrialReportFormData(SmallBatchTrialReportConditionModel Input) 
        {
            List<SmallBatchTrialReportFormModel> list = new List<SmallBatchTrialReportFormModel>();
            List<SqlParameter> sqlParameters = new List<SqlParameter>();

            string condition = "1=1 ";
            if (!string.IsNullOrWhiteSpace(Input.CurrentStatus))
            {
                condition += $"AND workflow.CurrentStatus = '{Input.CurrentStatus}' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.ApplicantEmployeeNumber))
            {
                condition += $"AND workflow.ApplicantEmployeeNumber = '{Input.ApplicantEmployeeNumber}' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.ProductLine))
            {
                condition += $"AND subtable.ProductLine = '{Input.ProductLine}' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.NewMaterialCode))
            {
                condition += $"AND host.NewMaterialCode LIKE '%{Input.NewMaterialCode}%' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.RawMaterialCode))
            {
                condition += $"AND host.RawMaterialCode LIKE '%{Input.RawMaterialCode}%' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.ApplicationNumber))
            {
                condition += $"AND workflow.ApplicationNumber LIKE '%{Input.ApplicationNumber}%' ";
            }
            if (!string.IsNullOrWhiteSpace(Input.ApplicantTime))
            {
                string[] timeArr = Input.ApplicantTime.Split(',');
                if (timeArr.Length > 0)
                {
                    condition += $"AND workflow.CreatedDate >= '{timeArr[0]} 00:00:00' AND workflow.CreatedDate <= '{timeArr[1]} 23:59:59' ";
                }
            }
            if (!string.IsNullOrWhiteSpace(Input.TrialSource))
            {
                condition += $"AND host.TrialSource = '{Input.TrialSource}' ";
            }

            //if (!string.IsNullOrWhiteSpace(input.AssetNo))
            //{
            //    condition += $"AND capitalSub.AssetNo LIKE '%'+@AssetNo+'%' ";
            //    SqlParameter sqlPara = new SqlParameter("@AssetNo", SqlDbType.NVarChar);
            //    sqlPara.Value = input.AssetNo;
            //    sqlParameters.Add(sqlPara);
            //}
            string SqlInsertClip = string.Empty;

            List<WorkflowProcInstIdModel> ProcInstIdList = new List<WorkflowProcInstIdModel>();

            //插入当前用户查询业务
            if (!string.IsNullOrWhiteSpace(Input.CurrentUser))
            {
                //获取当前用户资产赔偿电子流审批流程记录,去重后联查得到ProcInstId
                string _sql = $"SELECT a.ProcInstId,c.ApplicationNumber,c.ApproveDate FROM AssetsManagement.dbo.SmallBatchTrialBusinessData a LEFT JOIN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = b.ApplicationNumber AND WorkflowName = '小批量试用跟踪电子流')  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData b WHERE WorkflowName = '小批量试用跟踪电子流' AND ApproverName = '{Input.CurrentUser}') c ON c.ApplicationNumber = a.ProcessFolio WHERE ProcInstId !='' AND ApproveDate IS NOT NULL";
                ProcInstIdList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());

                //审批历史ProcInstId
                var ApprovalHistoryProcInstIdList = ProcInstIdList.Select(a => a.ProcInstId).ToList();

                //当前用户工号
                string employeeNumber = Input.CurrentUser.Substring(0, Input.CurrentUser.IndexOf(" "));
                //利用UserID获取当前用户工号LaunchList
                //var EmployeeInfo = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(x => x.UserId == UserId);
                if (!string.IsNullOrWhiteSpace(employeeNumber))
                {

                    //获取发起流程实例
                    _sql = $"SELECT ProcInstId FROM VW_Common.dbo.WorkflowApplicationData WHERE ProcInstId !='' AND WorkflowName='小批量试用跟踪电子流' AND ApplicantEmployeeNumber = '{employeeNumber}'";
                    List<WorkflowProcInstIdModel> LaunchList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());
                    if (LaunchList.Any())
                    {
                        var LaunchProcInstIdList = LaunchList.Select(a => a.ProcInstId).ToList();
                        ApprovalHistoryProcInstIdList.AddRange(LaunchProcInstIdList);
                        ApprovalHistoryProcInstIdList = ApprovalHistoryProcInstIdList.Distinct().ToList();
                    }

                }

                string ProcInstId_STR = "''";
                if (ApprovalHistoryProcInstIdList.Any())
                {
                    List<string> _ProcInstIdList = ApprovalHistoryProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                    ProcInstId_STR = string.Join(',', _ProcInstIdList);
                }

                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.SmallBatchTrialBusinessSubtableData WHERE ProcInstId IN({ProcInstId_STR}))";
            }
            else
            {
                SqlInsertClip = "(SELECT * FROM AssetsManagement.dbo.SmallBatchTrialBusinessSubtableData WHERE ProcInstId !='')";
            }

            string sql = @$"SELECT subtable.SysId AS [Key],TRIM(workflow.ApplicationNumber)AS ApplicationNumber,workflow.ProcInstId,workflow.ApplicantEmployeeNumber,TRIM(workflow.CurrentHandler) AS ApplicantName,workflow.CurrentStatus,workflow.CreatedDate AS ApplicantTime,workflow.SubmitBy,
                                   host.NewMaterialCode,host.RawMaterialCode,host.TrialSource,host.SQEEngineer,
                                   subtable.ProductLine,subtable.TrialBoardCabinetCode,subtable.SingleBoardCabinetType,subtable.UsingSingleBoardCabinetCount

                            FROM {SqlInsertClip} subtable 
                            LEFT JOIN AssetsManagement.dbo.SmallBatchTrialBusinessData host ON subtable.ProcInstId = host.ProcInstId 
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON subtable.ProcInstId = workflow.ProcInstId 
                            WHERE {condition}";


            list = SqlQuery<SmallBatchTrialReportFormModel>(sql, sqlParameters);

            //赋值流程最早时间
            if (!string.IsNullOrWhiteSpace(Input.CurrentUser))
            {
                list.ForEach((x) => {
                    var item = ProcInstIdList.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                    if (item != null)
                    {
                        x.ApplicantTime = item.ApproveDate;
                    }
                });
            }
            ////去掉当前审批人前缀
            //list.ForEach((x) => {
            //    if (!string.IsNullOrWhiteSpace(x.ApplicantName))
            //    {
            //        x.ApplicantName = x.ApplicantName.Substring(x.ApplicantName.IndexOf(" ") + 1).Trim();
            //    }
            //});

            return list;
        }

        /// <summary>
        /// 新增小批量试用跟踪电子流业务数据
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<int> AddSmallBatchTrialBusinessData(SmallBatchTrialBusinessDataModel Input, List<SmallBatchTrialBusinessSubtableDataModel> SubtableInput) 
        {
            var item = await dbContext.SmallBatchTrialBusinessData.FirstOrDefaultAsync(a => a.ProcInstId == Input.ProcInstId);
            if (item != null)
            {
                dbContext.SmallBatchTrialBusinessData.Remove(item);
            }

            var list = dbContext.SmallBatchTrialBusinessSubtableData.Where(a => a.ProcInstId == Input.ProcInstId);
            if (list.Count() > 0)
            {
                dbContext.SmallBatchTrialBusinessSubtableData.RemoveRange(list);
            }

            dbContext.SmallBatchTrialBusinessData.Add(Input);
            dbContext.SmallBatchTrialBusinessSubtableData.AddRange(SubtableInput);
            return await dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 查询加工-产品-人员配置数据(分页)
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<List<ProductProcessingPersonnelModel>> ProductProcessingPersonnelData(ProductProcessingPersonnelConditionModel Input)
        {
            if (Input != null)
            {
                var list = await dbContext.ProductProcessingPersonnel.WhereIf(!string.IsNullOrWhiteSpace(Input.key), a => a.ProcessingType == Input.key || a.ProductLine == Input.key).ToListAsync();

                return list;
            }
            else 
            {
                return await dbContext.ProductProcessingPersonnel.ToListAsync();
            }
        }

        /// <summary>
        /// 查询加工-产品-人员配置数据
        /// </summary>
        /// <returns></returns>
        public async Task<List<ProductProcessingPersonnelModel>> ProductProcessingPersonnelDataList() 
        {
            return await dbContext.ProductProcessingPersonnel.ToListAsync();
        }

        /// <summary>
        /// 新增加工-产品-人员配置数据
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<int> AddProductProcessingPersonnelData(ProductProcessingPersonnelModel Input) 
        {
            int count = 0;
            if (Input != null)
            {
                Input.SysId = Guid.NewGuid();
                dbContext.ProductProcessingPersonnel.Add(Input);
                return await dbContext.SaveChangesAsync();
            }
            else 
            {
                return count;
            }
        }

        /// <summary>
        /// 更新加工-产品-人员配置数据
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<int> EditProductProcessingPersonnelData(ProductProcessingPersonnelModel Input)
        {
            int count = 0;
            if (Input != null)
            {
                var item = dbContext.ProductProcessingPersonnel.FirstOrDefault(a => a.SysId == Input.SysId);
                if (item != null) 
                {
                    item.ProcessingType = Input.ProcessingType;
                    item.ProductLine = Input.ProductLine;
                    item.Planner = Input.Planner;
                    item.PlannerID = Input.PlannerID;
                    item.OutboundName = Input.OutboundName;
                    item.OutboundNameID = Input.OutboundNameID;
                    item.Processors = Input.Processors;
                    item.ProcessorsID = Input.ProcessorsID;
                    item.MaterialController = Input.MaterialController;
                    item.MaterialControllerID = Input.MaterialControllerID;
                    item.WarehousingName = Input.WarehousingName;
                    item.WarehousingNameID = Input.WarehousingNameID;
                    item.Purchaser = Input.Purchaser;
                    item.PurchaserID = Input.PurchaserID;
                    item.OutsourcingSQE = Input.OutsourcingSQE;
                    item.OutsourcingSQEID = Input.OutsourcingSQEID;
                    item.SectionChief = Input.SectionChief;
                    item.SectionChiefID = Input.SectionChiefID;
                    item.CcUserName = Input.CcUserName;
                    item.CcUserNameID = Input.CcUserNameID;
                    item.Remarks = Input.Remarks;
                    item.LastUpdatedBy = Input.LastUpdatedBy;
                    item.LastUpdateDate = Input.LastUpdateDate;

                    dbContext.ProductProcessingPersonnel.Attach(item);
                    count= await dbContext.SaveChangesAsync();
                }
                return count;
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// 删除加工-产品-人员配置数据
        /// </summary>
        /// <param name="SysId"></param>
        /// <returns></returns>
        public async Task<int> DeleteProductProcessingPersonnelData(Guid SysId)
        {
            int count = 0;
            var item = dbContext.ProductProcessingPersonnel.FirstOrDefault(a => a.SysId == SysId);
            if (item != null)
            {
                dbContext.ProductProcessingPersonnel.Remove(item);
                return await dbContext.SaveChangesAsync();
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// 查询产品线信息配置
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<List<ProductLineDetailsModel>> ProductLineDetailsData(ProductLineDetailsConditionModel Input) 
        {
            if (Input != null)
            {
                var list = await dbContext.ProductLineDetails.WhereIf(!string.IsNullOrWhiteSpace(Input.key), a => a.ProductLine == Input.key).ToListAsync();

                return list;
            }
            else
            {
                return await dbContext.ProductLineDetails.ToListAsync();
            }
        }

        /// <summary>
        /// 新增产品线信息配置
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<int> AddProductLineDetailsData(ProductLineDetailsModel Input) {
            int count = 0;
            if (Input != null)
            {
                Input.SysId = Guid.NewGuid();
                dbContext.ProductLineDetails.Add(Input);
                return await dbContext.SaveChangesAsync();
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// 更新产品线信息配置
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<int> EditProductLineDetailsData(ProductLineDetailsModel Input) 
        {
            int count = 0;
            if (Input != null)
            {
                var item = dbContext.ProductLineDetails.FirstOrDefault(a => a.SysId == Input.SysId);
                if (item != null)
                {
                    item.ProductLine = Input.ProductLine;
                    item.EngineerID = Input.EngineerID;
                    item.EngineerName = Input.EngineerName;
                    item.PlanningEngineerID = Input.PlanningEngineerID;
                    item.PlanningEngineerName = Input.PlanningEngineerName;
                    item.ProductLineQualityManagerID = Input.ProductLineQualityManagerID;
                    item.ProductLineQualityManagerName = Input.ProductLineQualityManagerName;
                    item.ProductLineCommissionerID = Input.ProductLineCommissionerID;
                    item.ProductLineCommissionerName = Input.ProductLineCommissionerName;
                    item.CcUserName = Input.CcUserName;
                    item.CcUserID = Input.CcUserID;
                    item.ProcessReviewName = Input.ProcessReviewName;
                    item.ProcessReviewID = Input.ProcessReviewID;
                    item.TrackingEngineerName = Input.TrackingEngineerName;
                    item.TrackingEngineerID = Input.TrackingEngineerID;
                    item.Remarks = Input.Remarks;
                    item.LastUpdatedBy = Input.LastUpdatedBy;
                    item.LastUpdateDate = Input.LastUpdateDate;

                    dbContext.ProductLineDetails.Attach(item);
                    count = await dbContext.SaveChangesAsync();
                }
                return count;
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// 删除产品线信息配置
        /// </summary>
        /// <param name="SysId"></param>
        /// <returns></returns>
        public async Task<int> DeleteProductLineDetailsData(Guid SysId) 
        {
            int count = 0;
            var item = dbContext.ProductLineDetails.FirstOrDefault(a => a.SysId == SysId);
            if (item != null)
            {
                dbContext.ProductLineDetails.Remove(item);
                return await dbContext.SaveChangesAsync();
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// 查询产品线信息配置-条件检索
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<List<ProductLineDetailsModel>> SearchProductLineDetailsData(ProductLineDetailsConditionModel Input) 
        {
            if (Input != null)
            {
                var list = await dbContext.ProductLineDetails.WhereIf(!string.IsNullOrWhiteSpace(Input.key), a => a.ProductLine.Contains(Input.key) || a.ProductLineQualityManagerName.Contains(Input.key) || a.ProductLineCommissionerName.Contains(Input.key)).ToListAsync();

                return list;
            }
            else
            {
                return await dbContext.ProductLineDetails.ToListAsync();
            }
        }

        /// <summary>
        /// 返回产品线跟踪工程师
        /// </summary>
        /// <returns></returns>
        public async Task<List<SmallBatchTrialPrackingEngineerModel>> TrialPrackingEngineerDetailsDataList() 
        {
            var list = await dbContext.ProductLineDetails.Select(a => new SmallBatchTrialPrackingEngineerModel
            {
                TrackingEngineerName = a.TrackingEngineerName ?? "",
                TrackingEngineerID = a.TrackingEngineerID ?? ""
            }).ToListAsync();
            return list;
        }

        /// <summary>
        /// 返回与父流程相关子流程信息
        /// </summary>
        /// <param name="FatherProcInstId"></param>
        /// <returns></returns>
        public List<ProcessAssociatedInformationModel> SelectProcessAssociatedInformationList(string FatherProcInstId) 
        {
            List<ProcessAssociatedInformationModel> list = new List<ProcessAssociatedInformationModel>();
            string sql = @$"SELECT a.SysId AS [Key],a.ProcInstId,a.TrialBoardCabinetCode,a.SingleBoardCabinetType,b.CurrentHandler AS CurrentApprover,b.CurrentStatus AS CurrentState,b.ApplicationNumber AS SerialNumber,b.CreatedDate,'OutsourcingProcessing' AS ProcessType FROM AssetsManagement.dbo.OutsourcingProcessingBusinessData a
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData b ON a.ProcInstId = b.ProcInstId WHERE a.FatherProcInstId ='{FatherProcInstId}' AND a.ProcInstId !=''
                            UNION
                            SELECT a.SysId AS [Key],a.ProcInstId,a.CabinetCode AS TrialBoardCabinetCode,a.CabinetName AS SingleBoardCabinetType,b.CurrentHandler AS CurrentApprover,b.CurrentStatus AS CurrentState,b.ApplicationNumber AS SerialNumber,b.CreatedDate,'WorkshopProcessin' AS ProcessType FROM AssetsManagement.dbo.WorkshopProcessinReportData a
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData b ON a.ProcInstId = b.ProcInstId WHERE a.ParentFlowId='{FatherProcInstId}' AND a.ProcInstId !=''";
            list = SqlQuery<ProcessAssociatedInformationModel>(sql, new List<SqlParameter>());
            return list;
        }

        /// <summary>
        /// 修改表单试用范围-确定稿
        /// </summary>
        /// <returns></returns>
        public async Task<int> UpdateDetermineTrackMaterialTrackingFromJsonData(DetermineTrackMaterialTrackingModel DetermineTrackMaterialTracking) 
        {
            int count = 0;
            if (DetermineTrackMaterialTracking != null) 
            {
                var item = await workflowCommonDataContext.ApplicationData.FirstOrDefaultAsync(a => a.ProcInstId == DetermineTrackMaterialTracking.ProcInstID);
                if (item != null) 
                {
                    DetermineTrackMaterialTracking.determineTrackMaterialTracking.ForEach((x) => {
                        x.addType = "1";
                    });
                    JObject Json = (JObject)JsonConvert.DeserializeObject(item.FormData);
                    Json["task1"]["determineTrackMaterialTrackingList"] = JArray.Parse(DetermineTrackMaterialTracking.determineTrackMaterialTracking.ToJsonString());

                    //dynamic dyn = JsonConvert.DeserializeObject<dynamic>(item.FormData);
                    //dyn.task1.determineTrackMaterialTrackingList = JsonConvert.DeserializeObject<List<ProcessAssociatedInformationModel>>(dyn.task1.determineTrackMaterialTrackingList);
                    //dyn.task1.determineTrackMaterialTrackingList = DetermineTrackMaterialTracking.determineTrackMaterialTracking;

                    item.FormData = JsonConvert.SerializeObject(Json);
                    workflowCommonDataContext.ApplicationData.Attach(item);
                    count = await workflowCommonDataContext.SaveChangesAsync();
                }
                
            }
            return count;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public List<T> SqlQuery<T>(string sql, List<SqlParameter> sqlParameters) where T : class, new()
        {
            List<T> list = new List<T>();
            var conn = dbContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0)
                {
                    sqlParameters.ForEach((x) => {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var columnName = dr.GetName(i);
                        var colDataType = dr.GetFieldType(i);
                        var item = dr.GetFieldValue<object>(i);
                        if (item == DBNull.Value)
                        {
                            item = null;
                        }
                        //var fieid = t.GetType().GetField(columnName);
                        //fieid?.SetValue(t, item);
                        var property = t.GetType().GetProperty(columnName);
                        property?.SetValue(t, item);
                    }
                    list.Add(t);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return list;
        }
    }
}
